Option Strict On

Imports Excel = Microsoft.Office.Interop.Excel
Imports System.Text.RegularExpressions


Public Class Formulas

    Public Shared Function Find(ByVal rangeToSearch As Excel.Range) As IList
        Dim rngFound As Excel.Range
        Dim findList As ArrayList = New ArrayList

        Dim rngFoundFirst As Excel.Range

        rngFound = rangeToSearch.Find( _
            "=", , _
            Excel.XlFindLookIn.xlFormulas, Excel.XlLookAt.xlPart, _
            Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, False)

        While Not rngFound Is Nothing
            If rngFoundFirst Is Nothing Then
                rngFoundFirst = rngFound
            ElseIf rngFound.Address = rngFoundFirst.Address Then
                Exit While
            End If
            findList.Add(CreateTemplateFormula(rngFound))
            rngFound = rangeToSearch.FindNext(rngFound)
        End While
        Return findList
    End Function

    Private Shared Function CreateTemplateFormula(ByRef location As Excel.Range) As ITemplateFormula
        Dim tFormula As ITemplateFormula
        If (HasAreaReference(location.Formula.ToString())) Then
            tFormula = New TemplateAreaFormula(location)
        Else
            tFormula = New TemplateFormula(location)
        End If
        Return tFormula
    End Function

    Private Shared Function HasAreaReference(ByVal formula As String) As Boolean
        Return Regex.Match(formula, "(?<ref>\w{1,2}\d{1,5}):\k<ref>").Success
    End Function

End Class
